package kr.co.choongang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.activation.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class EmpDAO {
	
	DataSource dataFactory;

	public EmpDAO() {
		try {
			Context ctx=new InitialContext();
			dataFactory=(DataSource)ctx.lookup("java:comp/env/jdbc/Oracle11g");
		} catch (NamingException e) {
			
			e.printStackTrace();
		}
	}
	public ArrayList<EmpDTO> select() {
		ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		
		try {
			con=((Statement) dataFactory).getConnection();
			String sql="select emp_id, ename, salary, depart from emp2";
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				String emp_id=rs.getString("emp_id");
				String ename=rs.getString("ename");
				int salary=rs.getInt("salary");
				String depart=rs.getString("depart");
			
				EmpDTO dto=new EmpDTO(emp_id, ename, salary, depart);
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			
				try {
					if(rs!=null)rs.close();
					if(pstmt!=null)pstmt.close();
					if(con!=null)con.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
		
		}//end select
}//end class
	
	
	/*String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	String userid = "noir";
	String passwd = "dkdlxl";
		

	public EmpDAO() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	public ArrayList<EmpDTO> select() {
	ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
	Connection con=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	
	
	try {
		con=DriverManager.getConnection(url, userid, passwd);
		String sql="select emp_id, ename, salary, depart from emp2";
		pstmt=con.prepareStatement(sql);
		rs=pstmt.executeQuery();
		while(rs.next()){
			String emp_id=rs.getString("emp_id");
			String ename=rs.getString("ename");
			int salary=rs.getInt("salary");
			String depart=rs.getString("depart");
		
			EmpDTO dto=new EmpDTO(emp_id, ename, salary, depart);
			list.add(dto);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally {
		
			try {
				if(rs!=null)rs.close();
				if(pstmt!=null)pstmt.close();
				if(con!=null)con.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
	return list;
	
	}//end select
	
	public int insert(String emp_id, String ename, int salary, String depart) {
		
		Connection con=null;
		PreparedStatement pstmt=null;
		
		int n=0;
			try {
				con=DriverManager.getConnection(url, userid, passwd);
				String sql = "insert into emp2  values(emp_id=?,ename=?,salary=?,depart=?)";
				pstmt=con.prepareStatement(sql);
				pstmt.setString(1, emp_id);
				pstmt.setString(2, ename);
				pstmt.setInt(3, salary);
				pstmt.setString(4, depart);
				n=pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return n;
		
		}//end insert
	
public int delete(String emp_id) {
		
		Connection con=null;
		PreparedStatement pstmt=null;
		
		int n=0;
			try {
				con=DriverManager.getConnection(url, userid, passwd);
				String sql="delete from emp2 where emp_id=?";
				pstmt=con.prepareStatement(sql);
				pstmt.setString(1, emp_id);
				n=pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return n;
		
		}//end delete
public int update(String emp_id, String ename, int salary, String depart) {
	
	Connection con=null;
	PreparedStatement pstmt=null;
	
	int n=0;
		try {
			con=DriverManager.getConnection(url, userid, passwd);
			String sql="update emp2 set emp_id=?, ename=?, salary=?, depart=? where emp_id=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, emp_id);
			pstmt.setString(2, ename);
			pstmt.setInt(3, salary);
			pstmt.setString(4, depart);
			pstmt.setString(5, emp_id);
			n=pstmt.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	
	return n;
	
	}//end delete
	*/
//end class



